package com.erp.app.admin.sysParam.dao;

//JRE Classes
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import com.erp.app.admin.sysParam.beans.SysParamVO;
import com.erp.app.admin.sysParam.ejb.SysParamPK;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.exceptions.CreateRecordException;
import com.erp.framework.exceptions.DeleteRecordException;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.exceptions.ModifyRecordException;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.util.logger.Logger;

public class SysParamDAO extends SimplePageEditStyleDao {
	
	protected String sqlPurge(){
        return sqlDelete();
    }

    protected void setValuesForPurge(PreparedStatement pStmt, Object obj) throws SQLException {
        setValuesForDelete(pStmt, obj);
    }

    public ChildList selectByParent(SimpleBaseVo parent, Connection conn) throws FindRecordException {

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        ChildList rslt = new ChildList(SysParamVO.class);

        try {

            pStmt = conn.prepareStatement(sqlList());
            setValuesForList(pStmt, parent.getPrimaryKey());

            rs = pStmt.executeQuery();

            while (rs.next()) {
                SysParamVO sysParamVO = (SysParamVO) getResultSetValues(rs);
                sysParamVO.setIsDirty(false);
                sysParamVO.setIsExisting(true);
                sysParamVO.setParent(parent);

                rslt.add(sysParamVO);
            }
        } catch (SQLException sqe) {
        	Logger.logExceptions(this, sqe);
            throw new FindRecordException("Error in SysParamVO.selectByParent: " + sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
        }
        return rslt;
    }

    public SimpleBaseVo findByPrimaryKey(Object obj, Connection conn) throws FindRecordException {

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        SysParamVO sysParamVO = null;

        try {
            SysParamPK pk = (SysParamPK) obj;
            pStmt = conn.prepareStatement(sqlFind());
            setValuesForFind(pStmt, pk);

            rs = pStmt.executeQuery();
            if (rs.next()) {
                sysParamVO = (SysParamVO) getResultSetValues(rs);
            }
        } catch (SQLException sqe) {
        	Logger.logExceptions(this, sqe);
            throw new FindRecordException("Error in SysParamDAO.findByPrimaryKey: " + sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
        }
        return sysParamVO;
    }

    public void insert(SimpleBaseVo bizModel, Connection conn) throws CreateRecordException {

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            SysParamVO sysParamVO = (SysParamVO) bizModel;
            pStmt = conn.prepareStatement(sqlInsert());
            setValuesForInsert(pStmt, sysParamVO);

            int count = pStmt.executeUpdate();
            
            if (count != 1)
                throw new SQLException("Unable to insert in SysParamDAO: " + sysParamVO.getPrimaryKey());
        } catch (SQLException sqe) {
        	Logger.logExceptions(this, sqe);
            throw new CreateRecordException("Error in SysParamDAO.insert: " + sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
        }
    }

    public void update(SimpleBaseVo bizModel, Connection conn) throws ModifyRecordException {

        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            SysParamVO sysParamVO = (SysParamVO) bizModel;
            pStmt = conn.prepareStatement(sqlUpdate() + " AND " + updateDateStr(sysParamVO.getUpdateDate()));
            setValuesForModify(pStmt, sysParamVO);

            int count = pStmt.executeUpdate();
            if (count != 1)
                throw new SQLException("The record may have been modified in SysParamDAO! " + sysParamVO.getPrimaryKey());
        } catch (SQLException sqe) {
        	Logger.logExceptions(this, sqe);
            throw new ModifyRecordException("Error in SysParamDAO.update: " + sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
        }
    }

    public void remove(SimpleBaseVo bizModel, Connection conn) throws DeleteRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            SysParamVO sysParamVO = (SysParamVO) bizModel;
            pStmt = conn.prepareStatement(sqlDelete());
            setValuesForDelete(pStmt, sysParamVO);

            int count = pStmt.executeUpdate();
            if (count != 1)
                throw new SQLException("Unable to delete in SysParamDAO: " + sysParamVO.getPrimaryKey());
        } catch (SQLException sqe) {
        	Logger.logExceptions(this, sqe);
            throw new DeleteRecordException("Error in SysParamDAO.remove: " + sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
        }
    }

    protected String sqlInsert() {
        return "INSERT INTO T_SYS_PARAM (" + "PARAM_ID, PARAM_VAL, PARAM_DESC, CREATE_USER, CREATE_DATE) "
                + "VALUES (?,?,?,?,SYSDATE)";
    }

    protected void setValuesForInsert(PreparedStatement pStmt, Object obj) throws SQLException {
        SysParamVO sysParamVO = (SysParamVO) obj;
        int i = 1;
        pStmt.setString(i++, sysParamVO.getParamId());
        setNullableString(pStmt, i++, sysParamVO.getParamVal());
        setNullableString(pStmt, i++, sysParamVO.getParamDesc());
        setNullableString(pStmt, i++, sysParamVO.getCreateUser());
    }

    protected String sqlUpdate() {
        return "UPDATE T_SYS_PARAM SET " + "PARAM_VAL=?, PARAM_DESC=?, UPDATE_USER=?, UPDATE_DATE=SYSDATE "
                + "WHERE PARAM_ID = ? ";
    }

    protected void setValuesForModify(PreparedStatement pStmt, Object obj) throws SQLException {
        SysParamVO sysParamVO = (SysParamVO) obj;
        int i = 1;
        setNullableString(pStmt, i++, sysParamVO.getParamVal());
        setNullableString(pStmt, i++, sysParamVO.getParamDesc());
        setNullableString(pStmt, i++, sysParamVO.getUpdateUser());
        pStmt.setString(i++, sysParamVO.getParamId());
    }

    protected String sqlDelete() {
        return "DELETE FROM T_SYS_PARAM" + " WHERE PARAM_ID = ? ";
    }

    protected void setValuesForDelete(PreparedStatement pStmt, Object obj) throws SQLException {
        SysParamVO sysParamVO = (SysParamVO) obj;
        int i = 1;
        pStmt.setString(i++, sysParamVO.getParamId());
    }

    protected String sqlFind() {
        return "SELECT PARAM_ID, PARAM_VAL, PARAM_DESC, CREATE_USER, CREATE_DATE, UPDATE_USER" + ", UPDATE_DATE "
                + "FROM T_SYS_PARAM " + "WHERE PARAM_ID = ? ";

    }

    protected void setValuesForFind(PreparedStatement pStmt, Object obj) throws SQLException {
        SysParamPK pk = (SysParamPK) obj;
        int i = 1;
        pStmt.setString(i++, pk.getParamId());
    }

    protected String sqlList() {
        return "SELECT PARAM_ID, PARAM_VAL, PARAM_DESC, CREATE_USER, CREATE_DATE, UPDATE_USER" + ", UPDATE_DATE "
                + "FROM T_SYS_PARAM " + "WHERE PARAM_ID = ? ";
    }

    protected void setValuesForList(PreparedStatement pStmt, Object obj) throws SQLException {
        SysParamPK pk = (SysParamPK) obj;
        int i = 1;
        pStmt.setString(i++, pk.getParamId());
    }

    public Collection findByCriteria(Object obj, Connection conn) throws FindRecordException {

        // set the search criteria class
        // CompanyMasterSrchCriteria criteria = (CompanyMasterSrchCriteria) obj;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList rslt = new ArrayList();
        // String whereClause = toWhereClause(criteria);
        try {
            String query = "SELECT PARAM_ID, PARAM_VAL, PARAM_DESC, CREATE_USER, CREATE_DATE, UPDATE_USER" + ", UPDATE_DATE "
                    + "FROM T_SYS_PARAM "
                    // +whereClause
                    + " order by param_id";
            stmt = conn.createStatement();

            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    rslt.add((SysParamVO) getResultSetValues(rs));
                }
            }
        } catch (SQLException sqe) {
        	Logger.logExceptions(this, sqe);
            throw new FindRecordException("Error in SysParamDAO.findByCriteria: " + sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
        }
        return rslt;
    }

    /*
     * private String toWhereClause(CompanyMasterSrchCriteria criteria) {
     * StringBuffer whereClause = new StringBuffer();
     * 
     * if (criteria.getSrchCompanyName() != null) {
     * whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
     * whereClause.append("UPPER(SHORT_NAME) =
     * '"+criteria.getSrchCompanyName().trim().toUpperCase()+"' "); }
     * 
     * return whereClause.toString(); }
     */

    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
        SysParamVO sysParamVO = new SysParamVO();
        sysParamVO.setParamId(rs.getString("PARAM_ID"));
        sysParamVO.setParamVal(rs.getString("PARAM_VAL"));
        sysParamVO.setParamDesc(rs.getString("PARAM_DESC"));
        sysParamVO.setCreateUser(rs.getString("CREATE_USER"));
        sysParamVO.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        sysParamVO.setUpdateUser(rs.getString("UPDATE_USER"));
        sysParamVO.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        return sysParamVO;
    }

    public SysParamVO getSysParamFor(String paramId, Connection conn) throws FindRecordException {

        Statement stmt = null;
        ResultSet rs = null;
        SysParamVO sysParamVO = null;
        try {
            String query = "SELECT * FROM T_SYS_PARAM " + "WHERE PARAM_ID = '" + paramId.toUpperCase() + "'";
            stmt = conn.createStatement();

            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    sysParamVO = (SysParamVO) getResultSetValues(rs);
                }
            }
        } catch (Exception sqe) {
        	Logger.logExceptions(this, sqe);
            throw new FindRecordException("Error in SysParamDAO.getSysParamFor: " + sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
        }
        return sysParamVO;

    }

    public ChildList getAllSysParam(Connection conn) throws FindRecordException {

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        ChildList rslt = new ChildList(SysParamVO.class);
        try {
            pStmt = conn.prepareStatement(sqlGetAllSysParam());

            rs = pStmt.executeQuery();
            while (rs.next()) {
                SysParamVO vo = (SysParamVO) getResultSetValues(rs);
                rslt.add(vo);
            }
        } catch (SQLException sqe) {
        	Logger.logExceptions(this, sqe);
            throw new FindRecordException("Error in SysParamDAO.getAllSysParam: " + sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
        }
        return rslt;
    }

    protected String sqlGetAllSysParam() {
        return "SELECT * FROM T_SYS_PARAM " + "ORDER BY PARAM_ID";
    }

    public String getSysParamValFor(String paramId, Connection conn) throws FindRecordException {

        Statement stmt = null;
        ResultSet rs = null;
        String rslt = "";
        try {
            String query = "SELECT PARAM_VAL FROM T_SYS_PARAM " + "WHERE PARAM_ID = '" + paramId.toUpperCase() + "'";
            stmt = conn.createStatement();

            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    rslt = rs.getString("PARAM_VAL");
                }
            }
        } catch (Exception sqe) {
        	Logger.logExceptions(this, sqe);
            throw new FindRecordException("Error in SysParamDAO.getSysParamValFor: " + sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException sqe) {
            	Logger.logExceptions(this, sqe);
            }
        }
        return rslt;

    }
}
